package com.tjhk.test.util;

import cn.hutool.core.util.StrUtil;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.*;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.HashMap;
import java.util.Map;

/**
 * @author ：little grape
 * @description：TODO
 * @date ：2021/9/12 19:08
 */
@Slf4j
public class ExcelUtil {
    /**
     * 默认sheet名称
     */
    private static final String DEFAULT_SHEET_NAME = "sheet";
    /**
     * 单个Sheet页最大行数（除去标题）
     */
    private static final int SINGLE_SHEET_MAX_ROWS = 65535;
    /**
     * 默认单元格宽度
     */
    private static final int DEFAULT_CELL_WIDTH = 5000;
    /**
     * 默认日期格式
     */
    private static final String DEFAULT_DATE_PATTERN = "yyyy-MM-dd HH:mm:ss";

    /**
     * @param filePath
     * @return Map<String,String[][]>
     */
    public static Map<String, String[][]> readExcel(String filePath){
        Map<String, String[][]> result = new HashMap<>();
        try {
            //用流的方式先读取到你想要的excel的文件
            FileInputStream fis=new FileInputStream(new File(filePath));
            //获取整个excel
            XSSFWorkbook hb =new XSSFWorkbook(fis);
            for (int i = 0; i < hb.getNumberOfSheets(); i++) {
                //获取第一个表单sheet
                XSSFSheet sheet = hb.getSheetAt(i);
                //获取第一行
                int firstrow = sheet.getFirstRowNum();
                //获取最后一行
                int lastrow = sheet.getLastRowNum();

                String[][] cellValues = new String[lastrow+1][sheet.getRow(0).getLastCellNum()];
                //循环行数依次获取列数
                for (int j = firstrow; j < lastrow+1; j++) {
                    //获取哪一行i
                    Row row = sheet.getRow(j);
                    if (row != null) {
                        //获取这一行的第一列
                        int firstcell = row.getFirstCellNum();
                        //获取这一行的最后一列
                        int lastcell = row.getLastCellNum();
                        for (int k = firstcell; k <lastcell; k++) {
                            //获取第j列
                            Cell cell = row.getCell(k);
                            cellValues[j][k] = cell.toString();
//                            System.out.print(cell.toString()+"\t");
                        }
                    }
                }
                result.put(sheet.getSheetName(),cellValues);
            }
            fis.close();
        } catch (Exception e){
            System.out.println(e);
            return null;
        }
        return result;
    }
    public static Map<String, String[][]> readExcel(InputStream fis){
        Map<String, String[][]> result = new HashMap<>();
        try {
            //获取整个excel
            XSSFWorkbook hb =new XSSFWorkbook(fis);
            for (int i = 0; i < hb.getNumberOfSheets(); i++) {
                //获取第一个表单sheet
                XSSFSheet sheet = hb.getSheetAt(i);
                //获取第一行
                int firstrow = sheet.getFirstRowNum();
                //获取最后一行
                int lastrow = sheet.getLastRowNum();

                String[][] cellValues = new String[lastrow+1][sheet.getRow(0).getLastCellNum()];
                //循环行数依次获取列数
                for (int j = firstrow; j < lastrow+1; j++) {
                    //获取哪一行i
                    Row row = sheet.getRow(j);
                    if (row != null) {
                        //获取这一行的第一列
                        int firstcell = row.getFirstCellNum();
                        //获取这一行的最后一列
                        int lastcell = row.getLastCellNum();
                        for (int k = firstcell; k <lastcell; k++) {
                            //获取第j列
                            Cell cell = row.getCell(k);
                            cellValues[j][k] = getCellValueByCell(cell);
                        }
                    }
                }
                result.put(sheet.getSheetName(),cellValues);
            }
            fis.close();
        } catch (Exception e){
            System.out.println(e);
            return null;
        }
        return result;
    }
    //获取单元格各类型值，返回字符串类型
    public static String getCellValueByCell(Cell cell) {
        //判断是否为null或空串
        if (cell==null || cell.toString().trim().equals("")) {
            return "";
        }
        String cellValue = "";
        CellType cellType=cell.getCellType();
        switch (cellType) {

            case NUMERIC: // 数字
                short format = cell.getCellStyle().getDataFormat();
                if (DateUtil.isCellDateFormatted(cell)) {
                    SimpleDateFormat sdf = null;
                    //System.out.println("cell.getCellStyle().getDataFormat()="+cell.getCellStyle().getDataFormat());
                    if (format == 20 || format == 32) {
                        sdf = new SimpleDateFormat("HH:mm");
                    } else if (format == 14 || format == 31 || format == 57 || format == 58) {
                        // 处理自定义日期格式：m月d日(通过判断单元格的格式id解决，id的值是58)
                        sdf = new SimpleDateFormat("yyyy-MM-dd");
                        double value = cell.getNumericCellValue();
                        Date date = org.apache.poi.ss.usermodel.DateUtil
                                .getJavaDate(value);
                        cellValue = sdf.format(date);
                    }else {// 日期
                        sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                    }
                    try {
                        cellValue = sdf.format(cell.getDateCellValue());// 日期
                    } catch (Exception e) {
                        try {
                            throw new Exception("exception on get date data !".concat(e.toString()));
                        } catch (Exception e1) {
                            e1.printStackTrace();
                        }
                    }finally{
                        sdf = null;
                    }
                }  else {
                    BigDecimal bd = new BigDecimal(cell.getNumericCellValue());
                    cellValue = bd.toPlainString();// 数值 这种用BigDecimal包装再获取plainString，可以防止获取到科学计数值
                }
                break;
            case STRING: // 字符串
                cellValue = cell.getStringCellValue();
                break;
            case BOOLEAN: // Boolean
                cellValue = cell.getBooleanCellValue()+"";;
                break;
            case FORMULA: // 公式
                cellValue = cell.getCellFormula();
                break;
            case BLANK: // 空值
                cellValue = "";
                break;
            case ERROR: // 故障
                cellValue = "ERROR VALUE";
                break;
            default:
                cellValue = "UNKNOW VALUE";
                break;
        }
        return cellValue;
    }

    /**
     * 新生成XSSFWorkbook 写入数据
     * @param sheetName
     * @param title
     * @param data
     * @return
     */
    public static XSSFWorkbook writeExcel(String sheetName, String[] title, String[][] data) {
        //创建Excel工作簿
        XSSFWorkbook workbook = new XSSFWorkbook();
        //创建一个工作表sheet
        XSSFSheet sheet = workbook.createSheet(StrUtil.isBlank(sheetName)?DEFAULT_SHEET_NAME:sheetName);
        //创建第一行
        XSSFRow row = sheet.createRow(0);
        XSSFCell cell = null;
        //插入title
        for (int i = 0; i < title.length; i++) {
            cell = row.createCell(i);
            cell.setCellValue(title[i]);

        }
        //追加数据
        for (int i = 0; i < data.length; i++) {
            XSSFRow nextrow = sheet.createRow(i+1);
            for (int j = 0; j < data[0].length; j++) {
                XSSFCell cell2 = nextrow.createCell(j);
                cell2.setCellValue(data[i][j]);
            }
        }
        return workbook;
    }

    /**
     * 在现成的XSSFWorkbook上插入sheet写入数据
     * @param workbook
     * @param sheetName
     * @param title
     * @param data
     * @return
     */
    public static boolean writeExcelInWorkbook(XSSFWorkbook workbook, String sheetName, String[] title, String[][] data) {
        //创建一个工作表sheet
        XSSFSheet sheet = workbook.createSheet(StrUtil.isBlank(sheetName)?null:sheetName);
        //创建第一行
        XSSFRow row = sheet.createRow(0);
        XSSFCell cell = null;
        //插入title
        for (int i = 0; i < title.length; i++) {
            cell = row.createCell(i);
            cell.setCellValue(title[i]);

        }
        //追加数据
        for (int i = 0; i < data.length; i++) {
            XSSFRow nextrow = sheet.createRow(i+1);
            for (int j = 0; j < data[0].length; j++) {
                XSSFCell cell2 = nextrow.createCell(j);
                cell2.setCellValue(data[i][j]);
            }
        }
        return true;
    }

    public static boolean exportExcel(String filePath, XSSFWorkbook workbook){
        //创建文件
        // 以文件的形式输出工作簿对象
        FileOutputStream fileOut = null;
        try{
            File exportFile = new File(filePath);
            if (!exportFile.exists()) {
                exportFile.createNewFile();
            }
            // 以文件的形式输出工作簿对象
            fileOut = new FileOutputStream(filePath);
            workbook.write(fileOut);
            fileOut.flush();
        } catch (Exception e) {
            log.error("输出Excel时发生错误，错误原因：" + e.getMessage());
            return false;
        } finally {
            try {
                if (null != fileOut) {
                    fileOut.close();
                }
                if (null != workbook) {
                    workbook.close();
                }
            } catch (IOException e) {
                log.error("关闭输出流时发生错误，错误原因：" + e.getMessage());
                return false;
            }
        }
        return true;
    }
}
